package com.integration.platform.compareSQL;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Introduce 类简介
 * @Module 模块说明
 * @Author ASUS
 * @Date 2022/2/10 16:38
 * @Desc 功能描述: doc 文档 工具
 **/
public class CompareSQL {








    public static void main(String[] a){

        ConnectSQl currentDB=new ConnectSQl("localhost:3306","zdhy","root","root");
        currentDB.initTableSchema();
        currentDB.close();

        ConnectSQl oldDB=new ConnectSQl("localhost:3306","sim_zdhy1","root","root");
        oldDB.initTableSchema();
        oldDB.close();

        compareSchema(oldDB.tableSchema, currentDB.tableSchema);

        System.out.println("执行完毕");
    }

    public static void compareSchema(Map<String, Map<String,ColumnSchema>> oldSchema ,
                                     Map<String, Map<String,ColumnSchema>> currentSchema){
        if(oldSchema == null ){
            throw new RuntimeException("oldSchema is not null");
        }
        if(currentSchema == null ){
            throw new RuntimeException("currentSchema is not null");
        }
        // 第一步表的比较
        System.out.println("新库对比旧库 ------------------------------------------ ");
        for(Map.Entry<String, Map<String,ColumnSchema>> one : currentSchema.entrySet()){
            if(!oldSchema.containsKey(one.getKey())){
                System.out.println("旧库缺少的表："+one.getKey());
            }
        }
        System.out.println("旧库对比新库 ------------------------------------------ ");
        for(Map.Entry<String, Map<String,ColumnSchema>> one : oldSchema.entrySet()){
            if(!currentSchema.containsKey(one.getKey())){
                System.out.println("旧库多余的表："+one.getKey());
            }
        }
        System.out.println("");
        System.out.println("------------------------------------------ ");
        System.out.println("");
        System.out.println("表字段对比-以新库表为准 ------------------------------------------ ");
        System.out.println("");
        System.out.println("");
        for(Map.Entry<String, Map<String,ColumnSchema>> one : currentSchema.entrySet()){
            StringBuilder sb = new StringBuilder();
            if(oldSchema.containsKey(one.getKey())){
                // 都有表， 对比字段
                Map<String,ColumnSchema> currentColumn = one.getValue();
                Map<String,ColumnSchema> oldColumn = oldSchema.get(one.getKey());

                for(Map.Entry<String,ColumnSchema> columnOne : currentColumn.entrySet()){
                    if(!oldColumn.containsKey(columnOne.getKey())){
                        sb.append("缺少字段："+columnOne.getKey()).append("\n");
                    }
                }
                for(Map.Entry<String,ColumnSchema> columnOne : oldColumn.entrySet()){
                    if(!currentColumn.containsKey(columnOne.getKey())){
                        sb.append("多余字段："+columnOne.getKey()).append("\n");
                    }
                }
                for(Map.Entry<String,ColumnSchema> columnOne : currentColumn.entrySet()){
                    // 表的字段都一样，对比属性等等
                    if(oldColumn.containsKey(columnOne.getKey())){
                        boolean isDiff = false;
                        ColumnSchema current = columnOne.getValue();
                        ColumnSchema old = oldColumn.get(columnOne.getKey());
                        if(!current.getFieldType().equals(old.getFieldType())){
                            sb.append(columnOne.getKey()+"类型不一致").append("\n");
                            isDiff =true;
                        }
                        if(old.getDefaultValue() != null && old.getDefaultValue() != null){
                            if((old.getDefaultValue() != null && current.getDefaultValue() == null) ||
                                    (old.getDefaultValue() == null && current.getDefaultValue() != null) ||
                                    !current.getDefaultValue().equals(old.getDefaultValue())){
                                sb.append(columnOne.getKey()+"默认值不一致").append("\n");
                                isDiff =true;
                            }
                        }

                        if(!current.getColumnKey().equals(old.getColumnKey())){
                            sb.append(columnOne.getKey()+"索引键值不一致").append("\n");
                            isDiff =true;
                        }
                        if(!current.getNullable().equals(old.getNullable())){
                            sb.append(columnOne.getKey()+"可为null不一致").append("\n");
                            isDiff =true;
                        }
                        if(!current.getRemark().equals(old.getRemark())){
                            sb.append(columnOne.getKey()+"备注不一致").append("\n");
                            isDiff =true;
                        }
                        if(isDiff){
                            sb.append("---").append("\n");
                        }
                    }
                }

                if(sb.length()> 0){
                    System.out.println(one.getKey()+"：表对比 ------------------------------------------ ");
                    System.out.println(sb.toString());
                    sb = null;
                }
            }

        }





    }


    public static  class ConnectSQl{
        // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
        //static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        //static final String DB_URL = "jdbc:mysql://localhost:3306/runoob?characterEncoding=utf8&useSSL=true";

        // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
        private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
        Connection conn = null;
        Statement stmt = null;
        private String DB_URL = "";


        // 数据库的用户名与密码，需要根据自己的设置
        private String USER = "";
        private String PASS = "";
        private String DBNAME = "";

        // 数据库 + 表 + 字段结构
        //  Map<表名, Map<字段名,字段结构>>
        Map<String, Map<String,ColumnSchema>> tableSchema = new HashMap<>();


        public ConnectSQl(String hostPort, String dbName ,String name , String pwd) {
            if(hostPort == null || hostPort.isEmpty()){
                throw new RuntimeException("hostPort is not empty or null");
            }
            if(dbName == null || dbName.isEmpty()){
                throw new RuntimeException("dbName is not empty or null");
            }
            if(name == null || name.isEmpty()){
                throw new RuntimeException("name is not empty or null");
            }
            if(pwd == null || pwd.isEmpty()){
                throw new RuntimeException("pwd is not empty or null");
            }
            try {
                Class.forName(JDBC_DRIVER);
                System.out.println(hostPort+"/"+dbName+"：加载数据库驱动成功");
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                DB_URL =  "jdbc:mysql://"+hostPort+"/"+dbName+"?useSSL=false&serverTimezone=UTC";
                USER = name;
                PASS = pwd;
                DBNAME = dbName;
                conn = DriverManager.getConnection(DB_URL, name, pwd);
                System.out.println(hostPort+"/"+dbName+"：连接数据库驱动成功");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        public void initTableSchema(){
            if(conn == null){
                throw new RuntimeException(DBNAME+"：未连接成功数据库");
            }
            // 查询所有表
            queryAllTable();
            // 查询表结构
            for(Map.Entry<String, Map<String,ColumnSchema>> one : tableSchema.entrySet()){
                queryOneTableSchema(one.getKey(), one.getValue());
            }

        }

        public void close(){
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }



        private void queryAllTable() {
            try{
                stmt = conn.createStatement();
                String sql;
                sql = "select table_name from information_schema.tables where table_schema='"+DBNAME+"';";
                ResultSet rs = stmt.executeQuery(sql);

                // 展开结果集数据库
                while(rs.next()){
                    // 通过字段检索
                    String table_name  = rs.getString("table_name");
                    if(!tableSchema.containsKey(table_name)){
                        tableSchema.put(table_name, new HashMap<>());
                    }
                }
                // 完成后关闭
                rs.close();
                stmt.close();
            }catch(SQLException se){
                // 处理 JDBC 错误
                se.printStackTrace();
            }catch(Exception e){
                // 处理 Class.forName 错误
                e.printStackTrace();
            }finally{
                // 关闭资源
                try{
                    if(stmt!=null) stmt.close();
                }catch(SQLException se2){
                }
            }
        }


        private void queryOneTableSchema(String tableName, Map<String,ColumnSchema> schemaMap) {
            try{
                stmt = conn.createStatement();
                String sql;
                sql = "SELECT c.ORDINAL_POSITION AS no, c.COLUMN_NAME AS fieldName, c.COLUMN_TYPE AS fieldType, " +
                        "c.COLUMN_COMMENT AS remark, c.IS_NULLABLE AS nullable, " +
                        "c.COLUMN_DEFAULT AS defaultValue, c.COLUMN_KEY AS columnKey" +
                        " FROM " +
                        " information_schema. COLUMNS AS c " +
                        " WHERE " +
                        " table_name = '"+tableName+"' " +
                        " AND table_schema = '"+DBNAME+"' " +
                        " ORDER BY " +
                        " ORDINAL_POSITION ; ";
                ResultSet rs = stmt.executeQuery(sql);

                // 展开结果集数据库
                while(rs.next()){
                    // 通过字段检索
                    String fieldName  = rs.getString("fieldName");
                    if(!schemaMap.containsKey(fieldName)){
                        String no  = rs.getString("no");
                        String fieldType  = rs.getString("fieldType");
                        String remark  = rs.getString("remark");
                        String nullable  = rs.getString("nullable");
                        String defaultValue  = rs.getString("defaultValue");
                        String columnKey  = rs.getString("columnKey");
                        ColumnSchema columnSchema = new ColumnSchema(no , fieldName, fieldType, remark, nullable, defaultValue, columnKey);
                        schemaMap.put(fieldName, columnSchema);
                    }

                }
                // 完成后关闭
                rs.close();
                stmt.close();
            }catch(SQLException se){
                // 处理 JDBC 错误
                se.printStackTrace();
            }catch(Exception e){
                // 处理 Class.forName 错误
                e.printStackTrace();
            }finally{
                // 关闭资源
                try{
                    if(stmt!=null) stmt.close();
                }catch(SQLException se2){
                }// 什么都不做

            }
        }

    }




    public static class ColumnSchema{

        private String no;
        private String fieldName;
        private String fieldType;
        private String remark;
        private String nullable;
        private String defaultValue;
        private String columnKey;

        public ColumnSchema(){}

        public ColumnSchema(String no, String fieldName, String fieldType, String remark, String nullable, String defaultValue, String columnKey) {
            this.no = no;
            this.fieldName = fieldName==null?"":fieldName;
            this.fieldType = fieldType==null?"":fieldType;
            this.remark = remark==null?"":remark;
            this.nullable = nullable==null?"":nullable;
            this.defaultValue = defaultValue==null?"":defaultValue;
            this.columnKey = columnKey==null?"":columnKey;
        }

        public String getNo() {
            return no;
        }

        public void setNo(String no) {
            this.no = no;
        }

        public String getFieldName() {
            return fieldName;
        }

        public void setFieldName(String fieldName) {
            this.fieldName = fieldName;
        }

        public String getFieldType() {
            return fieldType;
        }

        public void setFieldType(String fieldType) {
            this.fieldType = fieldType;
        }

        public String getRemark() {
            return remark;
        }

        public void setRemark(String remark) {
            this.remark = remark;
        }

        public String getNullable() {
            return nullable;
        }

        public void setNullable(String nullable) {
            this.nullable = nullable;
        }

        public String getDefaultValue() {
            return defaultValue;
        }

        public void setDefaultValue(String defaultValue) {
            this.defaultValue = defaultValue;
        }

        public String getColumnKey() {
            return columnKey;
        }

        public void setColumnKey(String columnKey) {
            this.columnKey = columnKey;
        }
    }


}
